﻿using System;
using System.Data;
using System.Configuration;

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;

/// <summary>
/// Summary description for constr123
/// </summary>
public class constr123
{
	public constr123()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    #region properties
    private SqlConnection _con;
    public SqlConnection con
    {

        get
        {
            return _con;
        }

        set
        {
            _con = value;
        }
    }
    private SqlCommand _com;
    public SqlCommand com
    {

        get
        {
            return _com;
        }

        set
        {
            _com = value;
        }
    }

    #endregion

    #region private methods
    private void openconnection()
    {
        if (_con == null)
        {
            //_con = new SqlConnection(WebConfigurationManager.ConnectionStrings["AccountsConnectionString"].ConnectionString);
            //if (HttpContext.Current.Session["ConnStr123"] != null)
            //{
                _con = new SqlConnection(HttpContext.Current.Session["constr123"].ToString());
            //}
            //else
            //{
            //    //HttpContext.Current.Response.Redirect("~/SessionExpired.aspx");
            //}
            _com = new SqlCommand();
            _com.Connection = _con;
        }
        if (_con.State == ConnectionState.Closed)
        {
            _con.Open();
        }

    }
    private void closeconnection()
    {
        if (_con.State == ConnectionState.Open)
        {
            _con.Close();
        }
    }

    private void disposeconnection()
    {
        if (_con.State == ConnectionState.Closed)
        {
            _con.Dispose();
            _con = null;
        }


    }




    #endregion

    #region public methods
    public DataTable getdata(string strsql)
    {
        openconnection();
        _com.CommandType = CommandType.Text;
        _com.CommandTimeout = 0;
        _com.CommandText = strsql;
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = _com;
        da.Fill(dt);
        closeconnection();
        disposeconnection();
        return dt;
    }

    public SqlDataReader getdatareader(string strsql)
    {
        openconnection();
        _com.CommandType = CommandType.Text;
        _com.CommandTimeout = 0;
        _com.CommandText = strsql;

        SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;
    }

    public DataSet getdataset(string strsql)
    {
        openconnection();
        _com.CommandType = CommandType.Text;
        _com.CommandTimeout = 0;
        _com.CommandText = strsql;
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = _com;
        da.Fill(ds);
        closeconnection();
        disposeconnection();
        return ds;
    }

    public int executesql(string strsql)
    {
        openconnection();
        _com.CommandType = CommandType.Text;
        _com.CommandTimeout = 0;
        _com.CommandText = strsql;
        int result = _com.ExecuteNonQuery();
        closeconnection();
        disposeconnection();
        return result;
    }

    public int executeprocedure(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;

        if (arrparam != null)
        {
            _com.Parameters.Clear();

            for (int i = 0; i < arrparam.Length; i++)
            {
                _com.Parameters.Add(arrparam[i]);

            }
        }

        int result = _com.ExecuteNonQuery();

        closeconnection();
        disposeconnection();
        return result;
    }
    public int executeprocedure(string spname)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;

        int result = _com.ExecuteNonQuery();

        closeconnection();
        disposeconnection();
        return result;
    }

    public int executeprocedure1(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;

        if (arrparam != null)
        {
            _com.Parameters.Clear();

            for (int i = 0; i < arrparam.Length; i++)
            {
                _com.Parameters.Add(arrparam[i]);

            }
        }

        int result = _com.ExecuteNonQuery();
        SqlDataReader dr = _com.ExecuteReader();
        closeconnection();
        disposeconnection();

        int r = (int)dr.GetValue(0);
        return r;
    }
    public string ExecuteProcedure_Scope_Identity(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;

        if (arrparam != null)
        {
            _com.Parameters.Clear();

            for (int i = 0; i < arrparam.Length; i++)
            {
                _com.Parameters.Add(arrparam[i]);

            }
        }
        string strResult = string.Empty;
        SqlDataReader dr = _com.ExecuteReader(CommandBehavior.CloseConnection);
        if (dr.HasRows)
        {
            dr.Read();
            strResult = dr[0].ToString();
        }
        closeconnection();
        disposeconnection();
        return strResult;
    }


    public DataTable getdatausingprocedure(string spname)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = _com;
        da.Fill(dt);
        closeconnection();
        disposeconnection();
        return dt;

    }

    public DataTable getdatausingprocedure(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;

        if (arrparam != null)
        {
            _com.Parameters.Clear();
            foreach (SqlParameter param in arrparam)
            {
                _com.Parameters.Add(param);
            }
        }


        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = _com;
        da.Fill(dt);
        _com.Parameters.Clear();
        closeconnection();
        disposeconnection();
        return dt;

    }

    public SqlDataReader GetDataReaderUsingProcedure(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;

        if (arrparam != null)
        {
            _com.Parameters.Clear();
            foreach (SqlParameter param in arrparam)
            {
                _com.Parameters.Add(param);
            }
        }

        SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection);
        _com.Parameters.Clear();
        return dr;
    }

    public SqlDataReader GetDataReaderUsingProcedure(string spname)
    {
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandTimeout = 0;
        _com.CommandText = spname;
        SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;
    }

    public bool isexist(string strsql)
    {
        openconnection();
        _com.CommandType = CommandType.Text;
        _com.CommandTimeout = 0;
        _com.CommandText = strsql;
        int result = (int)_com.ExecuteScalar();
        closeconnection();
        disposeconnection();

        if (result > 0)
        {
            return true;
        }
        else
        {
            return false;
        }


    }

    //public int executeprocedure(string spname, SqlParameter[] arrparam)
    //{
    //    openconnection();
    //    _com.CommandType = CommandType.StoredProcedure;
    //    _com.CommandTimeout = 0;
    //    _com.CommandText = spname;

    //    if (arrparam != null)
    //    {
    //        _com.Parameters.Clear();

    //        for (int i = 0; i < arrparam.Length; i++)
    //        {
    //            _com.Parameters.Add(arrparam[i]);

    //        }
    //    }

    //    int result = _com.ExecuteNonQuery();

    //    closeconnection();
    //    disposeconnection();
    //    return result;
    //}


    #endregion

}

